﻿Imports System.Data.SqlClient
Imports Control_Vuelos_BE
Imports GoEmail

Public Class ManifiestoADO
    ReadOnly MiConexion As New ConexionADO()
    ReadOnly cnx As New SqlConnection(MiConexion.GetCnx)
    ReadOnly cmd As SqlCommand

    Public Function pr_ActualizarCabecera(ByVal p_C_Manifiesto As C_ManifiestoBE) As Boolean
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction
            Dim sql As String = "UPDATE dbo.C_Manifiesto SET CodLugarOrigen=@CodLugarOrigen,CodLugarDestino=@CodLugarDestino,CodEmpresa=@CodEmpresa,FchViaje=@FchViaje,CodAeropuerto=@CodAeropuerto,CodResponsable=@CodResponsable," & _
                                "ObsCabecera=@ObsCabecera,ObsCabeceraManifiesto=@ObsCabeceraManifiesto,FlgValija=@FlgValija,CodModificadopor=@CodModificadopor,FchModificado=@FchModificado WHERE CodViaje=@CodViaje"
            Try
                Dim cmd3 As New SqlCommand(sql, cn)
                Using cmd3
                    cmd3.Parameters.AddWithValue("@CodLugarOrigen", p_C_Manifiesto.CodLugarOrigen)
                    cmd3.Parameters.AddWithValue("@CodLugarDestino", p_C_Manifiesto.CodLugarDestino)
                    cmd3.Parameters.AddWithValue("@CodEmpresa", p_C_Manifiesto.CodEmpresa)
                    cmd3.Parameters.AddWithValue("@FchViaje", p_C_Manifiesto.FchViaje)
                    cmd3.Parameters.AddWithValue("@CodAeropuerto", p_C_Manifiesto.CodAeropuerto)
                    cmd3.Parameters.AddWithValue("@CodResponsable", p_C_Manifiesto.CodResponsable)
                    cmd3.Parameters.AddWithValue("@ObsCabecera", p_C_Manifiesto.ObsCabecera.Trim)
                    cmd3.Parameters.AddWithValue("@ObsCabeceraManifiesto", p_C_Manifiesto.ObsCabeceraManifiesto.Trim)
                    cmd3.Parameters.AddWithValue("@FlgValija", p_C_Manifiesto.FlgValija)
                    cmd3.Parameters.AddWithValue("@CodViaje", p_C_Manifiesto.CodViaje)
                    cmd3.Parameters.AddWithValue("@CodModificadopor", p_C_Manifiesto.CodModificadopor)
                    cmd3.Parameters.AddWithValue("@FchModificado", p_C_Manifiesto.FchModificado)
                    cmd3.Transaction = oTran
                    cmd3.ExecuteNonQuery()
                End Using
                oTran.Commit()
                Return True
            Catch myException As Exception
                MsgBox("ManifiestoADO (pr_ActualizarCabecera) --> " & myException.Message, MsgBoxStyle.Critical)
                oTran.Rollback()
                Return False
            End Try
        End Using
    End Function

    Public Function pr_ActualizarEstado(ByVal p_CodViaje As String, ByVal p_EstadoManifiesto As String, ByVal p_EstadoVuelo As String) As Boolean
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction
            Dim cmd1 As SqlCommand
            Dim cmd2 As SqlCommand
            Try
                ' se quita la cabecera del manifiesto
                cmd1 = New SqlCommand("UPDATE dbo.C_Manifiesto SET EstadoManifiesto = @EstadoManifiesto WHERE (CodViaje = @CodViaje)", cn)
                cmd1.Transaction = oTran
                cmd1.Parameters.AddWithValue("@CodViaje", p_CodViaje.Trim)
                cmd1.Parameters.AddWithValue("@EstadoManifiesto", p_EstadoManifiesto.Trim)
                cmd1.ExecuteNonQuery()
                ' se cambia el estado del vuelo a cancelado
                cmd2 = New SqlCommand("UPDATE V_ProgramaViaje SET EstadoViaje=@EstadoViaje WHERE (CodViaje = @CodViaje)", cn)
                cmd2.Transaction = oTran
                cmd2.Parameters.AddWithValue("@CodViaje", p_CodViaje.Trim)
                cmd2.Parameters.AddWithValue("@EstadoViaje", p_EstadoVuelo.Trim)
                cmd2.ExecuteNonQuery()

                oTran.Commit()
                Return True
            Catch myException As Exception
                MsgBox("ManifiestoADO (pr_ActualizarEstado) --> " & myException.Message, MsgBoxStyle.Critical)
                oTran.Rollback()
                Return False
            End Try
        End Using
    End Function

    Public Function pr_ActualizarFlgLockReservaPasajero(ByVal sCodVuelo As String, ByVal sReserva As String) As Boolean
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction
            Dim cmd0 As SqlCommand
            Try
                ' actualizo el flgLock=1 para cada reserva
                cmd0 = New SqlCommand("UPDATE D_Manifiesto SET FlgLock=1 WHERE (CodViaje=@CodViaje) AND (CodReserva=@CodReserva)", cn)
                cmd0.Transaction = oTran
                cmd0.Parameters.AddWithValue("@CodViaje", sCodVuelo.Trim)
                cmd0.Parameters.AddWithValue("@CodReserva", sReserva.Trim)
                cmd0.ExecuteNonQuery()

                oTran.Commit()
                Return True
            Catch myException As Exception
                MsgBox("ManifiestoADO (pr_ActualizarReservaPasajero) --> " & myException.Message, MsgBoxStyle.Critical)
                oTran.Rollback()
                Return False
            End Try
        End Using
    End Function

    Public Function pr_ActualizarNroValija(ByVal p_CodViaje As String, ByVal p_NroValija As String) As Boolean
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction
            Try
                Dim cmd As New SqlCommand("UPDATE dbo.C_Manifiesto SET NroValija = @NroValija WHERE (CodViaje = @CodViaje)", cn)
                Using cmd
                    cmd.Parameters.AddWithValue("@CodViaje", p_CodViaje)
                    cmd.Parameters.AddWithValue("@NroValija", IIf(p_NroValija.Trim = "", p_CodViaje, p_NroValija))
                    cmd.Transaction = oTran
                    cmd.ExecuteNonQuery()
                End Using
                oTran.Commit()
                Return True
            Catch myException As Exception
                MsgBox("ManifiestoADO (pr_ActualizarNroValija) --> " & myException.Message, MsgBoxStyle.Critical)
                oTran.Rollback()
                Return False
            End Try
        End Using
    End Function

    Public Function pr_AgregarPasajero(ByVal p_D_Manifiesto As D_ManifiestoBE) As Boolean
        Try
            Dim sql As String = "INSERT INTO dbo.D_Manifiesto (CodViaje,CodReserva,NroDocumentoId,AppPasajero,ApmPasajero,NomPasajero,NacionalidadPasajero,CodAnalisis,CodComunidad,CodCentroCosto,PctCentroCosto,CodCentroCosto2,PctCentroCosto2,ObsManifiesto,FlgLock,FlgResponsable) " & _
                                " VALUES (@CodViaje,@CodReserva,@NroDocumentoId,@AppPasajero,@ApmPasajero,@NomPasajero,@NacionalidadPasajero,@CodAnalisis,@CodComunidad,@CodCentroCosto,@PctCentroCosto,@CodCentroCosto2,@ObsManifiesto,@PctCentroCosto2,@FlgLock,@FlgResponsable) "
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Dim oTran As SqlTransaction
            Using cn
                cn.Open()
                oTran = cn.BeginTransaction(IsolationLevel.Serializable)
                Dim cmd As New SqlCommand(sql, cn)
                Using cmd
                    cmd.Transaction = oTran
                    cmd.Parameters.AddWithValue("@CodReserva", p_D_Manifiesto.CodReserva)
                    cmd.Parameters.AddWithValue("@NroDocumentoId", p_D_Manifiesto.NroDocumentoId)
                    cmd.Parameters.AddWithValue("@CodViaje", p_D_Manifiesto.CodViaje)
                    cmd.Parameters.AddWithValue("@FlgResponsable", p_D_Manifiesto.FlgResponsable)
                    cmd.Parameters.AddWithValue("@NomPasajero", p_D_Manifiesto.NomPasajero)
                    cmd.Parameters.AddWithValue("@NacionalidadPasajero", p_D_Manifiesto.NacionalidadPasajero)
                    cmd.Parameters.AddWithValue("@ApmPasajero", p_D_Manifiesto.ApmPasajero)
                    cmd.Parameters.AddWithValue("@ObsManifiesto", p_D_Manifiesto.ObsManifiesto)
                    cmd.Parameters.AddWithValue("@AppPasajero", p_D_Manifiesto.AppPasajero)
                    cmd.Parameters.AddWithValue("@CodAnalisis", p_D_Manifiesto.CodAnalisis)
                    cmd.Parameters.AddWithValue("@CodComunidad", IIf(p_D_Manifiesto.CodComunidad.Trim = "", "0000000", p_D_Manifiesto.CodComunidad))
                    cmd.Parameters.AddWithValue("@CodCentroCosto", p_D_Manifiesto.CodCentroCosto)
                    cmd.Parameters.AddWithValue("@PctCentroCosto", p_D_Manifiesto.PctCentroCosto)
                    cmd.Parameters.AddWithValue("@CodCentroCosto2", p_D_Manifiesto.CodCentroCosto2)
                    cmd.Parameters.AddWithValue("@PctCentroCosto2", p_D_Manifiesto.PctCentroCosto2)
                    cmd.Parameters.AddWithValue("@FlgLock", p_D_Manifiesto.FlgLock)
                    cmd.ExecuteNonQuery()
                End Using
                Dim cmd1 As New SqlCommand("UPDATE V_Reserva SET EstadoReserva=3 WHERE (CodReserva=@CodReserva)", cn)
                Using cmd1
                    cmd1.Transaction = oTran
                    cmd1.Parameters.AddWithValue("@CodReserva", p_D_Manifiesto.CodReserva)
                    cmd1.ExecuteNonQuery()
                End Using
            End Using
            oTran.Commit()
            Return True
        Catch myException As Exception
            MsgBox("ManifiestoADO (pr_AgregarPasajeroManifiesto) --> " & myException.Message, MsgBoxStyle.Critical)
            Return False
        End Try
    End Function

    Public Function pr_AgregarPasajeroManifiesto(ByVal p_C_Manifiesto As C_ManifiestoBE, ByVal p_D_Manifiesto As D_ManifiestoBE) As Boolean
        Try
            Try
                Dim sqlc As String = "INSERT INTO dbo.C_Manifiesto(CodViaje, CodLugarOrigen, CodLugarDestino, CodEmpresa, FchViaje, HraViaje, CodAeropuerto, CodResponsable, ObsCabecera, ObsCabeceraManifiesto, EstadoManifiesto, FlgValija, CodCreadopor, FchCreado) " & _
                                     "VALUES (@CodViaje, @CodLugarOrigen, @CodLugarDestino, @CodEmpresa, @FchViaje, @HraViaje, @CodAeropuerto, @CodResponsable, @ObsCabecera, @ObsCabeceraManifiesto, @EstadoManifiesto, @FlgValija, @CodCreadopor, @FchCreado)"
                Dim sqld As String = "INSERT INTO dbo.D_Manifiesto(CodViaje,CodReserva,NroDocumentoId,AppPasajero,ApmPasajero,NomPasajero,NacionalidadPasajero,CodAnalisis,CodComunidad,CodCentroCosto,PctCentroCosto,CodCentroCosto2,PctCentroCosto2,ObsManifiesto,FlgResponsable,FlgLock) " & _
                                     " VALUES (@CodViaje,@CodReserva,@NroDocumentoId,@AppPasajero,@ApmPasajero,@NomPasajero,@NacionalidadPasajero,@CodAnalisis,@CodComunidad,@CodCentroCosto,@PctCentroCosto,@CodCentroCosto2,@PctCentroCosto2,@ObsManifiesto,@FlgResponsable,@FlgLock) "
                Dim oTran As SqlTransaction
                Dim cn As New SqlConnection(MiConexion.GetCnx)
                Using cn
                    cn.Open()
                    oTran = cn.BeginTransaction(IsolationLevel.Serializable)
                    Dim cmd As New SqlCommand("SELECT COUNT(*) FROM dbo.C_Manifiesto WHERE CodViaje = @CodViaje", cn)
                    Using cmd
                        cmd.CommandType = CommandType.Text
                        cmd.Parameters.Add("@CodViaje", SqlDbType.Char, 12)
                        cmd.Parameters("@CodViaje").Value = p_C_Manifiesto.CodViaje.Trim
                        cmd.Transaction = oTran
                        Dim t As Integer = CInt(cmd.ExecuteScalar())
                        If t > 0 Then
                        Else
                            Dim cmd1 As New SqlCommand(sqlc, cn)
                            Using cmd1
                                cmd1.Parameters.AddWithValue("@CodViaje", p_C_Manifiesto.CodViaje)
                                cmd1.Parameters.AddWithValue("@CodLugarOrigen", p_C_Manifiesto.CodLugarOrigen)
                                cmd1.Parameters.AddWithValue("@CodLugarDestino", p_C_Manifiesto.CodLugarDestino)
                                cmd1.Parameters.AddWithValue("@CodEmpresa", p_C_Manifiesto.CodEmpresa)
                                cmd1.Parameters.AddWithValue("@FchViaje", p_C_Manifiesto.FchViaje)
                                cmd1.Parameters.AddWithValue("@HraViaje", p_C_Manifiesto.HraViaje)
                                cmd1.Parameters.AddWithValue("@CodAeropuerto", p_C_Manifiesto.CodAeropuerto)
                                cmd1.Parameters.AddWithValue("@CodResponsable", p_C_Manifiesto.CodResponsable)
                                cmd1.Parameters.AddWithValue("@ObsCabecera", p_C_Manifiesto.ObsCabecera)
                                cmd1.Parameters.AddWithValue("@ObsCabeceraManifiesto", p_C_Manifiesto.ObsCabeceraManifiesto)
                                cmd1.Parameters.AddWithValue("@EstadoManifiesto", p_C_Manifiesto.EstadoManifiesto)
                                cmd1.Parameters.AddWithValue("@FlgValija", p_C_Manifiesto.FlgValija)
                                cmd1.Parameters.AddWithValue("@CodCreadopor", p_C_Manifiesto.CodCreadopor)
                                cmd1.Parameters.AddWithValue("@FchCreado", p_C_Manifiesto.FchCreado)
                                cmd1.Transaction = oTran
                                cmd1.ExecuteNonQuery()
                            End Using
                        End If
                        Dim cmd2 As New SqlCommand(sqld, cn)
                        Using cmd2
                            cmd2.Transaction = oTran
                            cmd2.Parameters.AddWithValue("@CodViaje", p_D_Manifiesto.CodViaje)
                            cmd2.Parameters.AddWithValue("@CodReserva", p_D_Manifiesto.CodReserva)
                            cmd2.Parameters.AddWithValue("@NroDocumentoId", p_D_Manifiesto.NroDocumentoId)
                            cmd2.Parameters.AddWithValue("@AppPasajero", p_D_Manifiesto.AppPasajero)
                            cmd2.Parameters.AddWithValue("@ApmPasajero", p_D_Manifiesto.ApmPasajero)
                            cmd2.Parameters.AddWithValue("@NomPasajero", p_D_Manifiesto.NomPasajero)
                            cmd2.Parameters.AddWithValue("@NacionalidadPasajero", p_D_Manifiesto.NacionalidadPasajero)
                            cmd2.Parameters.AddWithValue("@CodAnalisis", p_D_Manifiesto.CodAnalisis)
                            cmd2.Parameters.AddWithValue("@CodComunidad", IIf(p_D_Manifiesto.CodComunidad.Trim = "", "0000000", p_D_Manifiesto.CodComunidad))
                            cmd2.Parameters.AddWithValue("@CodCentroCosto", p_D_Manifiesto.CodCentroCosto)
                            cmd2.Parameters.AddWithValue("@PctCentroCosto", p_D_Manifiesto.PctCentroCosto)
                            cmd2.Parameters.AddWithValue("@CodCentroCosto2", p_D_Manifiesto.CodCentroCosto2)
                            cmd2.Parameters.AddWithValue("@PctCentroCosto2", p_D_Manifiesto.PctCentroCosto2)
                            cmd2.Parameters.AddWithValue("@ObsManifiesto", p_D_Manifiesto.ObsManifiesto)
                            cmd2.Parameters.AddWithValue("@FlgResponsable", p_D_Manifiesto.FlgResponsable)
                            cmd2.Parameters.AddWithValue("@FlgLock", p_D_Manifiesto.FlgLock)
                            cmd2.ExecuteNonQuery()
                        End Using

                        Dim cmd4 As New SqlCommand("UPDATE V_Reserva SET EstadoReserva = 3, FchViaje = @FchViaje WHERE (CodReserva = @CodReserva)", cn)
                        Using cmd4
                            cmd4.Transaction = oTran
                            cmd4.Parameters.AddWithValue("@CodReserva", p_D_Manifiesto.CodReserva)
                            cmd4.Parameters.AddWithValue("@FchViaje", p_C_Manifiesto.FchViaje)
                            cmd4.ExecuteNonQuery()
                        End Using

                    End Using
                    oTran.Commit()
                    Return True
                End Using
            Catch myException As Exception
                MsgBox("ManifiestoADO (pr_AgregarPasajeroManifiesto) --> " & myException.Message, MsgBoxStyle.Critical)
                '                oTran.Rollback()
                Return False
            End Try
        Catch myException As Exception
            MsgBox("ManifiestoADO (pr_AgregarPasajeroManifiesto) --> " & myException.Message, MsgBoxStyle.Critical)
            Return False
        End Try
    End Function

    Public Function pr_GetDetalleManifiesto(ByVal vCodman As String) As DataTable
        Using dts As New DataSet()
            Dim ada As SqlDataAdapter
            Try
                cmd.Connection = cnx
                cmd.CommandType = CommandType.StoredProcedure
                cmd.CommandText = "sp_ListaDetalleManifiestoxCodigo"
                cmd.Parameters.Clear()
                cmd.Parameters.Add(New SqlParameter("@CodViaje", SqlDbType.Char, 12))
                cmd.Parameters("@CodViaje").Value = vCodman
                ada = New SqlDataAdapter(cmd)
                ada.Fill(dts, "C_Manifiesto")
            Catch ex As Exception
                Throw New Exception(ex.Message)
            End Try
            Return dts.Tables("C_Manifiesto")
        End Using
    End Function

    Public Function pr_GetManifiesto(ByVal p_EstManifiesto As String) As DataTable
        Try
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Using cn
                Dim cmd As New SqlCommand("sp_ListadeManifiestoCerrado", cn)
                Using cmd
                    cmd.CommandType = Data.CommandType.StoredProcedure
                    cmd.Parameters.Add("@p_EstadoManifiesto", SqlDbType.Char, 1)
                    cmd.Parameters("@p_EstadoManifiesto").Value = p_EstManifiesto.Trim
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable("C_Manifiesto")
                    da.Fill(dt)
                    Return dt
                End Using
            End Using
        Catch myException As Exception
            MsgBox("ManifiestoADO (pb_getManifiesto) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_ListarManifiesto(ByVal p_CodManifiesto As String) As DataTable
        Try
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Using cn
                Dim cmd As New SqlCommand("sp_Listar_Manifiesto", cn)
                Using cmd
                    cmd.CommandType = Data.CommandType.StoredProcedure
                    cmd.Parameters.Add("@p_CodViaje", SqlDbType.Char, 12)
                    cmd.Parameters("@p_CodViaje").Value = p_CodManifiesto.Trim
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable("Manifiesto")
                    da.Fill(dt)
                    Return dt
                End Using
            End Using
        Catch myException As Exception
            MsgBox("ManifiestoADO (ListarManifiesto) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_ListarManifiestoBuscar() As DataTable
        Try
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Using cn
                Dim cmd As New SqlCommand("sp_Listar_Manifiesto_Buscar", cn)
                Using cmd
                    cmd.CommandType = Data.CommandType.StoredProcedure
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable("Manifiesto")
                    da.Fill(dt)
                    Return dt
                End Using
            End Using
        Catch myException As Exception
            MsgBox("ManifiestoADO (ListarManifiestoBuscar) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_NotificarPasajeroManifiesto(ByVal p_CodViaje As String) As String
        Dim lFlgOk As Boolean = False
        Dim sRelacion As String = ""
        Dim mensaje As String
        Dim sql As String = "SELECT cm.CodViaje,dm.CodReserva,cm.FchViaje,cm.HraViaje,ae.NomAeropuerto,ld.NomLugar,dm.NomPasajero+' '+dm.AppPasajero+' '+dm.ApmPasajero AS Pasajero,mo.desMotivo,dm.NroDocumentoId,dm.NacionalidadPasajero,pa.MailPasajero FROM dbo.c_Manifiesto cm " & _
                            "INNER JOIN dbo.d_Manifiesto dm ON cm.codviaje=dm.codviaje " & _
                            "INNER JOIN dbo.v_Reserva re ON dm.CodReserva=re.CodReserva " & _
                            "INNER JOIN dbo.m_Pasajero pa ON re.CodPasajero=pa.CodPasajero " & _
                            "INNER JOIN dbo.m_MotivoViaje mo ON re.MotivoViaje=mo.CodMotivo " & _
                            "INNER JOIN dbo.m_lugar ld ON cm.codlugardestino=ld.codlugar " & _
                            "INNER JOIN dbo.m_Aeropuerto ae ON cm.CodLugarOrigen=ae.CodOrigen AND cm.codAeropuerto=ae.nroCorrelativo WHERE (cm.codviaje=@CodViaje)"
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim cmd As New SqlCommand(sql, cn)
            cmd.Parameters.AddWithValue("@CodViaje", p_CodViaje)
            Try
                Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                Using dt As DataTable = New DataTable("Manifiesto")
                    da.Fill(dt)
                    Dim drCurrent As DataRow
                    For Each drCurrent In dt.Rows
                        Dim sReserva As String = drCurrent("CodReserva")
                        Dim sPasajero As String = drCurrent("Pasajero")
                        Dim sFecha As String = CType(drCurrent("FchViaje"), String)
                        Dim sAeropuerto As String = drCurrent("NomAeropuerto")
                        Dim sHora As String = drCurrent("HraViaje")
                        Dim sLugar As String = drCurrent("NomLugar")
                        Dim sMotivo As String = drCurrent("DesMotivo")
                        Dim spara As String = drCurrent("MailPasajero")
                        mensaje = String.Format("Sr. {0}:{1}Se le comunica que Ud. tiene una reserva de viaje para el día {2} para viajar a {3}.{1}Presentarse en el Aeropuerto {4} a las {5} am. {1}Motivo de viaje: {6} Manifiesto: {7}", sPasajero.Trim, vbCrLf, sFecha.Trim, sLugar.Trim, sAeropuerto.Trim, sHora.Trim, sMotivo.Trim, p_CodViaje)
                        Dim x As New EnviarEmail()
                        lFlgOk = x.EnviarMail(spara.Trim, "Notificación de reserva de viaje", mensaje, "Gestión Humana", "juanblasmiranda@gmail.com", "juan20111")
                        If lFlgOk Then
                            pr_ActualizarFlgLockReservaPasajero(p_CodViaje, sReserva)
                        End If
                        sRelacion = String.Format("{0}{1}  ({2}){3}", sRelacion, sPasajero, IIf(lFlgOk = True, "NOTIFICADO", "ERROR"), vbCrLf)
                    Next
                End Using
                Return sRelacion
            Catch myException As Exception
                MsgBox("ManifiestoADO (pr_NotificarPasajeroManifiesto) --> " & myException.Message, MsgBoxStyle.Critical)
                Return ""
            End Try
        End Using
    End Function

    Public Function pr_RecuperarCabecera(ByVal p_CodViaje As String) As C_ManifiestoBE
        Dim _C_ManifiestoBE As New C_ManifiestoBE
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim cmd2 As New SqlCommand("SELECT * FROM dbo.C_Manifiesto WHERE (CodViaje = @CodViaje)", cn)
            cmd2.Parameters.AddWithValue("@CodViaje", p_CodViaje)
            Try
                Dim da As SqlDataAdapter = New SqlDataAdapter(cmd2)
                Using dt As DataTable = New DataTable("Cabecera")
                    da.Fill(dt)
                    Dim drCurrent As DataRow
                    For Each drCurrent In dt.Rows
                        _C_ManifiestoBE.CodViaje = drCurrent("CodViaje").ToString
                        _C_ManifiestoBE.CodLugarOrigen = drCurrent("CodLugarOrigen")
                        _C_ManifiestoBE.CodLugarDestino = drCurrent("CodLugarDestino")
                        _C_ManifiestoBE.CodEmpresa = drCurrent("CodEmpresa")
                        _C_ManifiestoBE.FchViaje = drCurrent("FchViaje")
                        _C_ManifiestoBE.HraViaje = drCurrent("HraViaje")
                        _C_ManifiestoBE.CodAeropuerto = drCurrent("CodAeropuerto")
                        _C_ManifiestoBE.CodResponsable = drCurrent("CodResponsable")
                        If IsDBNull(drCurrent("ObsCabecera")) Then
                            _C_ManifiestoBE.ObsCabecera = ""
                        Else
                            _C_ManifiestoBE.ObsCabecera = drCurrent("ObsCabecera")
                        End If
                        _C_ManifiestoBE.ObsCabeceraManifiesto = drCurrent("ObsCabeceraManifiesto")
                        _C_ManifiestoBE.EstadoManifiesto = drCurrent("EstadoManifiesto")
                        If IsDBNull(drCurrent("FlgProducto")) Then
                            _C_ManifiestoBE.FlgProducto = False
                        Else
                            _C_ManifiestoBE.FlgProducto = drCurrent("FlgProducto")
                        End If
                        If IsDBNull(drCurrent("FlgProvisionado")) Then
                            _C_ManifiestoBE.FlgProvisionado = False
                        Else
                            _C_ManifiestoBE.FlgProvisionado = drCurrent("FlgProvisionado")
                        End If
                        _C_ManifiestoBE.FlgValija = drCurrent("FlgValija")
                        If IsDBNull(drCurrent("NroValija")) Then
                            _C_ManifiestoBE.NroValija = ""
                        Else
                            _C_ManifiestoBE.NroValija = drCurrent("NroValija")
                        End If

                        If IsDBNull(drCurrent("FlgVacio")) Then
                            _C_ManifiestoBE.FlgVacio = False
                        Else
                            _C_ManifiestoBE.FlgVacio = drCurrent("FlgVacio")
                        End If
                    Next
                End Using
                Return _C_ManifiestoBE
            Catch myException As Exception
                MsgBox("ManifiestoADO (pr_RecuperarCabecera) --> " & myException.Message, MsgBoxStyle.Critical)
                Return Nothing
            End Try
        End Using
    End Function

    Public Function pr_RecuperarDetalle(ByVal p_CodViaje As String, ByVal p_CodReserva As Integer) As D_ManifiestoBE
        Dim _D_ManifiestoBE As New D_ManifiestoBE
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim cmd2 As New SqlCommand("SELECT * FROM dbo.D_Manifiesto WHERE (CodViaje = @CodViaje) AND (CodReserva = @CodReserva)", cn)
            cmd2.Parameters.AddWithValue("@CodViaje", p_CodViaje)
            cmd2.Parameters.AddWithValue("@CodReserva", p_CodReserva)
            Try
                Dim da As SqlDataAdapter = New SqlDataAdapter(cmd2)
                Using dt As DataTable = New DataTable("Detalle")
                    da.Fill(dt)
                    Dim drCurrent As DataRow
                    For Each drCurrent In dt.Rows
                        _D_ManifiestoBE.CodViaje = drCurrent("CodViaje").ToString
                        _D_ManifiestoBE.CodReserva = drCurrent("CodReserva")
                        _D_ManifiestoBE.NroItem = IIf(IsDBNull(drCurrent("NroItem")), "", drCurrent("NroItem"))
                        _D_ManifiestoBE.NroDocumentoId = drCurrent("NroDocumentoId")
                        _D_ManifiestoBE.AppPasajero = drCurrent("AppPasajero")
                        _D_ManifiestoBE.ApmPasajero = drCurrent("ApmPasajero")
                        _D_ManifiestoBE.NomPasajero = drCurrent("NomPasajero")
                        _D_ManifiestoBE.NacionalidadPasajero = drCurrent("NacionalidadPasajero")
                        _D_ManifiestoBE.CodAnalisis = drCurrent("CodAnalisis")
                        _D_ManifiestoBE.CodComunidad = IIf(IsDBNull(drCurrent("CodComunidad")) Or drCurrent("CodComunidad") = "0000000", "", drCurrent("CodComunidad"))
                        _D_ManifiestoBE.CodCentroCosto = drCurrent("CodCentroCosto")
                        _D_ManifiestoBE.PctCentroCosto = drCurrent("PctCentroCosto")
                        _D_ManifiestoBE.CodCentroCosto2 = drCurrent("CodCentroCosto2")
                        _D_ManifiestoBE.PctCentroCosto2 = drCurrent("PctCentroCosto2")
                        _D_ManifiestoBE.TipPorExcepcion = IIf(IsDBNull(drCurrent("TipPorExepcion")), "", drCurrent("TipPorExepcion"))
                        _D_ManifiestoBE.ObsManifiesto = drCurrent("ObsManifiesto")
                        _D_ManifiestoBE.MonPasaje = IIf(IsDBNull(drCurrent("MonPasaje")), 0.0, drCurrent("MonPasaje"))
                        _D_ManifiestoBE.FlgLock = IIf(IsDBNull(drCurrent("FlgLock")), 0, drCurrent("FlgLock"))
                        _D_ManifiestoBE.FlgResponsable = IIf(IsDBNull(drCurrent("FlgResponsable")), False, drCurrent("FlgResponsable"))
                        _D_ManifiestoBE.FlgNoEditar = IIf(IsDBNull(drCurrent("FlgNoEditar")), False, drCurrent("FlgNoEditar"))
                        _D_ManifiestoBE.FlgChecking = IIf(IsDBNull(drCurrent("FlgChecking")), False, drCurrent("FlgChecking"))
                    Next
                End Using
                Return _D_ManifiestoBE
            Catch myException As Exception
                MsgBox("ManifiestoADO (pr_RecuperarDetalle) --> " & myException.Message, MsgBoxStyle.Critical)
                Return Nothing
            End Try
        End Using
    End Function

    Public Function pr_RecuperarListaManifiestoImpresion(ByVal p_CodViaje As String) As DataTable
        Try
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Using cn
                Dim cmd As New SqlCommand("sp_Listar_Manifiesto_Impresion", cn)
                Using cmd
                    cmd.CommandType = Data.CommandType.StoredProcedure
                    cmd.Parameters.Add("@p_CodViaje", SqlDbType.Char, 12)
                    cmd.Parameters("@p_CodViaje").Value = p_CodViaje.Trim
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable("Manifiesto")
                    da.Fill(dt)
                    Return dt
                End Using
            End Using
        Catch myException As Exception
            MsgBox("ManifiestoADO (pr_RecuperarListaManifiestoImpresion) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_RecuperarResponsable(ByVal p_Origen As String) As M_ResponsableVueloBE
        Dim _ResponsableBE As New M_ResponsableVueloBE
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim cmd2 As New SqlCommand("SELECT nroCorrelativo, nomResponsable, telResponsable FROM dbo.M_ResponsableVuelo WHERE (codOrigen = @codOrigen)", cn)
            cmd2.Parameters.AddWithValue("@codOrigen", p_Origen)
            Try
                Dim da As SqlDataAdapter = New SqlDataAdapter(cmd2)
                Using dt As DataTable = New DataTable("Detalle")
                    da.Fill(dt)
                    Dim drCurrent As DataRow
                    For Each drCurrent In dt.Rows
                        _ResponsableBE.NroCorrelativo = drCurrent("nroCorrelativo")
                        _ResponsableBE.NomResponsable = drCurrent("nomResponsable")
                        _ResponsableBE.TelResponsable = drCurrent("telResponsable")
                    Next
                End Using
                Return _ResponsableBE
            Catch myException As Exception
                MsgBox("ManifiestoADO (pr_RecuperarResponsable) --> " & myException.Message, MsgBoxStyle.Critical)
                Return Nothing
            End Try
        End Using
    End Function

    Public Function pr_RemoverPasajeroManifiesto(ByVal p_D_Manifiesto As D_ManifiestoBE) As Boolean
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Using oTran As SqlTransaction = cn.BeginTransaction(IsolationLevel.Serializable)
                Dim cmd2 As New SqlCommand("DELETE FROM dbo.D_Manifiesto WHERE (CodViaje = @CodViaje) AND (CodReserva = @CodReserva)", cn) With {.Transaction = oTran}
                cmd2.Parameters.AddWithValue("@CodViaje", p_D_Manifiesto.CodViaje)
                cmd2.Parameters.AddWithValue("@CodReserva", p_D_Manifiesto.CodReserva)
                Try
                    cmd2.ExecuteNonQuery()
                    Dim cmd4 As New SqlCommand("UPDATE V_Reserva SET EstadoReserva=2 WHERE (CodReserva = @CodReserva)", cn) With {.Transaction = oTran}
                    cmd4.Parameters.AddWithValue("@CodReserva", p_D_Manifiesto.CodReserva)
                    cmd4.ExecuteNonQuery()
                    oTran.Commit()
                    Return True
                Catch myException As Exception
                    MsgBox("ManifiestoADO (pr_RemoverPasajeroManifiesto) --> " & myException.Message, MsgBoxStyle.Critical)
                    oTran.Rollback()
                    Return False
                End Try
            End Using
        End Using
    End Function

    Public Function pr_UpdateCabeceraManifiestoViaje(ByVal p_C_Manifiesto As C_ManifiestoBE) As Boolean
        Dim sInstruccion As String = ""
        Dim sInstruccion1 As String = ""
        sInstruccion = "SELECT count(*) FROM dbo.C_Manifiesto WHERE CodViaje = @CodViaje"
        Dim oTran As SqlTransaction
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            oTran = cn.BeginTransaction(IsolationLevel.Serializable)
            Dim cmd As New SqlCommand(sInstruccion, cn)
            Using cmd
                cmd.CommandType = CommandType.Text
                cmd.Parameters.Add("@CodViaje", SqlDbType.Char, 12)
                cmd.Parameters("@CodViaje").Value = p_C_Manifiesto.CodViaje.Trim
                cmd.Transaction = oTran
                Dim t As Integer = CInt(cmd.ExecuteScalar())
                If t > 0 Then
                Else
                    sInstruccion1 = "INSERT INTO dbo.C_Manifiesto(CodViaje, CodLugarOrigen, CodLugarDestino, CodEmpresa, FchViaje, HraViaje, CodAeropuerto, CodResponsable, ObsCabecera, ObsCabeceraManifiesto, EstadoManifiesto, FlgValija, CodCreadopor, FchCreado) " & _
                                    "VALUES (@CodViaje, @CodLugarOrigen, @CodLugarDestino, @CodEmpresa, @FchViaje, @HraViaje, @CodAeropuerto, @CodResponsable, @ObsCabecera, @ObsCabeceraManifiesto, @EstadoManifiesto, @FlgValija, @CodCreadopor, @FchCreado)"
                    Dim cmd1 As New SqlCommand(sInstruccion1, cn)
                    Using cmd1
                        cmd1.Parameters.AddWithValue("@CodViaje", p_C_Manifiesto.CodViaje)
                        cmd1.Parameters.AddWithValue("@CodLugarOrigen", p_C_Manifiesto.CodLugarOrigen)
                        cmd1.Parameters.AddWithValue("@CodLugarDestino", p_C_Manifiesto.CodLugarDestino)
                        cmd1.Parameters.AddWithValue("@CodEmpresa", p_C_Manifiesto.CodEmpresa)
                        cmd1.Parameters.AddWithValue("@FchViaje", p_C_Manifiesto.FchViaje)
                        cmd1.Parameters.AddWithValue("@HraViaje", p_C_Manifiesto.HraViaje)
                        cmd1.Parameters.AddWithValue("@CodAeropuerto", p_C_Manifiesto.CodAeropuerto)
                        cmd1.Parameters.AddWithValue("@CodResponsable", p_C_Manifiesto.CodResponsable)
                        cmd1.Parameters.AddWithValue("@ObsCabecera", p_C_Manifiesto.ObsCabecera)
                        cmd1.Parameters.AddWithValue("@ObsCabeceraManifiesto", p_C_Manifiesto.ObsCabeceraManifiesto)
                        cmd1.Parameters.AddWithValue("@EstadoManifiesto", p_C_Manifiesto.EstadoManifiesto)
                        cmd1.Parameters.AddWithValue("@FlgValija", p_C_Manifiesto.FlgValija)
                        cmd1.Parameters.AddWithValue("@CodCreadopor", p_C_Manifiesto.CodCreadopor)
                        cmd1.Parameters.AddWithValue("@FchCreado", p_C_Manifiesto.FchCreado)
                        cmd1.Transaction = oTran
                        cmd1.ExecuteNonQuery()
                        oTran.Commit()
                        Return True
                    End Using
                End If
            End Using
        End Using
    End Function

    Public Function pr_UpdateDetalleManifiestoViaje(ByVal p_CodViaje As String, ByVal p_CodReserva As String, ByVal p_CodViajeChange As String) As Boolean
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction
            Dim mySqlCommand As SqlCommand
            Dim cmd3 As SqlCommand
            Dim cmd4 As SqlCommand
            Dim cmd5 As SqlCommand
            Dim cmd6 As SqlCommand
            Dim sInstruccion As String = "Update dbo.D_Manifiesto Set CodViaje=@CodViajeChange WHERE (CodViaje=@CodViaje) AND (CodReserva=@CodReserva)"
            Dim sInstruccion1 As String = "Update dbo.V_Valija Set CodViaje=@CodViajeChange WHERE (CodViaje=@CodViaje)"
            Dim sInstruccion2 As String = "Update dbo.C_Manifiesto_Adjunto Set CodViaje=@CodViajeChange WHERE (CodViaje=@CodViaje)"
            Dim sInstruccion3 As String = "Update dbo.C_Manifiesto Set C_Manifiesto.FchViaje =  (SELECT FchViaje FROM V_ProgramaViaje WHERE CodViaje = @CodViaje) WHERE CodViaje = @CodViajeChange "
            If p_CodReserva.Trim = "" Then
                sInstruccion = "Update dbo.D_Manifiesto Set CodViaje=@CodViajeChange WHERE (CodViaje=@CodViaje)"
            End If
            Try
                mySqlCommand = New SqlCommand(sInstruccion, cn)
                mySqlCommand.Transaction = oTran
                mySqlCommand.Parameters.AddWithValue("@CodViaje", p_CodViaje.Trim)
                If p_CodReserva.Trim <> "" Then
                    mySqlCommand.Parameters.AddWithValue("@CodReserva", p_CodReserva.Trim)
                End If
                mySqlCommand.Parameters.AddWithValue("@CodViajeChange", p_CodViajeChange.Trim)
                mySqlCommand.ExecuteNonQuery()
                ' se cambia el estado del vuelo a cancelado
                If p_CodReserva.Trim = "" Then
                    cmd3 = New SqlCommand("UPDATE V_ProgramaViaje SET EstadoViaje='9' WHERE (CodViaje = @CodViaje)", cn)
                    cmd3.Transaction = oTran
                    cmd3.Parameters.AddWithValue("@CodViaje", p_CodViaje.Trim)
                    cmd3.ExecuteNonQuery()
                End If
                cmd4 = New SqlCommand(sInstruccion1, cn)
                cmd4.Transaction = oTran
                cmd4.Parameters.AddWithValue("@CodViaje", p_CodViaje.Trim)
                cmd4.Parameters.AddWithValue("@CodViajeChange", p_CodViajeChange.Trim)
                cmd4.ExecuteNonQuery()
                cmd5 = New SqlCommand(sInstruccion2, cn)
                cmd5.Transaction = oTran
                cmd5.Parameters.AddWithValue("@CodViaje", p_CodViaje.Trim)
                cmd5.Parameters.AddWithValue("@CodViajeChange", p_CodViajeChange.Trim)
                cmd5.ExecuteNonQuery()

                cmd6 = New SqlCommand(sInstruccion3, cn)
                cmd6.Transaction = oTran
                cmd6.Parameters.AddWithValue("@CodViaje", p_CodViaje.Trim)
                cmd6.Parameters.AddWithValue("@CodViajeChange", p_CodViajeChange.Trim)
                cmd6.ExecuteNonQuery()

                oTran.Commit()
                Return True
            Catch myException As Exception
                MsgBox("ManifiestoADO (pr_UpdateDetalleManifiestoViaje) --> " & myException.Message, MsgBoxStyle.Critical)
                oTran.Rollback()
                Return False
            End Try
        End Using
    End Function

    Public Function pr_UpdateDetalleManifiestoLock(ByVal p_CodViaje As String, ByVal p_CodReserva As String, ByVal p_FlgLock As Int32) As Boolean
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction
            Dim mySqlCommand As SqlCommand
            Try
                mySqlCommand = New SqlCommand("Update dbo.D_Manifiesto Set FlgLock = @FlgLock  WHERE (CodViaje = @CodViaje) AND (CodReserva = @CodReserva)", cn)
                mySqlCommand.Transaction = oTran
                mySqlCommand.Parameters.AddWithValue("@CodViaje", p_CodViaje.Trim)
                mySqlCommand.Parameters.AddWithValue("@CodReserva", p_CodReserva.Trim)
                mySqlCommand.Parameters.AddWithValue("@FlgLock", p_FlgLock)
                mySqlCommand.ExecuteNonQuery()
                oTran.Commit()
                Return True
            Catch myException As Exception
                MsgBox("ManifiestoADO (pr_UpdateDetalleManifiestoLock) --> " & myException.Message, MsgBoxStyle.Critical)
                oTran.Rollback()
                Return False
            End Try
        End Using
    End Function

    Public Function pr_UpdateDetalleManifiestoResponsable(ByVal p_CodViaje As String, ByVal p_CodReserva As String, ByVal p_FlgResponsable As Byte) As Boolean
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction
            Dim mySqlCommand As SqlCommand
            Try
                mySqlCommand = New SqlCommand("Update dbo.D_Manifiesto Set FlgResponsable = @FlgResponsable  WHERE (CodViaje = @CodViaje) AND (CodReserva = @CodReserva)", cn)
                mySqlCommand.Transaction = oTran
                mySqlCommand.Parameters.AddWithValue("@CodViaje", p_CodViaje.Trim)
                mySqlCommand.Parameters.AddWithValue("@CodReserva", p_CodReserva.Trim)
                mySqlCommand.Parameters.AddWithValue("@FlgResponsable", p_FlgResponsable)
                mySqlCommand.ExecuteNonQuery()
                oTran.Commit()
                Return True
            Catch myException As Exception
                MsgBox("ManifiestoADO (pr_UpdateDetalleManifiestoResponsable) --> " & myException.Message, MsgBoxStyle.Critical)
                oTran.Rollback()
                Return False
            End Try
        End Using
    End Function

    Public Function pr_UpdateManifiestoCancelado(ByVal p_CodViaje As String) As Boolean
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction
            Dim cmd As SqlCommand
            Dim cmd2 As SqlCommand
            Dim cmd3 As SqlCommand
            Dim cmd4 As SqlCommand
            Dim cmd5 As SqlCommand
            Dim cmd6 As SqlCommand
            Try
                ' se devuele a estado habilitado las reservas
                cmd = New SqlCommand("UPDATE v_reserva SET EstadoReserva='2' WHERE CodReserva IN (SELECT CodReserva FROM d_manifiesto WHERE (codviaje = @CodViaje))", cn)
                cmd.Transaction = oTran
                cmd.Parameters.AddWithValue("@CodViaje", p_CodViaje.Trim)
                cmd.ExecuteNonQuery()
                cmd6 = New SqlCommand("DELETE FROM V_Valija WHERE (CodViaje = @CodViaje)", cn)
                cmd6.Transaction = oTran
                cmd6.Parameters.AddWithValue("@CodViaje", p_CodViaje.Trim)
                cmd6.ExecuteNonQuery()
                ' se quita los pasajeros del manifiesto
                cmd2 = New SqlCommand("DELETE FROM d_manifiesto WHERE (codviaje = @CodViaje)", cn)
                cmd2.Transaction = oTran
                cmd2.Parameters.AddWithValue("@CodViaje", p_CodViaje.Trim)
                cmd2.ExecuteNonQuery()
                ' se quita los datos adicionales del manifiesto
                cmd3 = New SqlCommand("DELETE FROM c_manifiesto_adjunto WHERE (codviaje = @CodViaje)", cn)
                cmd3.Transaction = oTran
                cmd3.Parameters.AddWithValue("@CodViaje", p_CodViaje.Trim)
                cmd3.ExecuteNonQuery()
                ' se quita la cabecera del manifiesto
                cmd4 = New SqlCommand("DELETE FROM c_manifiesto WHERE (codviaje = @CodViaje)", cn)
                cmd4.Transaction = oTran
                cmd4.Parameters.AddWithValue("@CodViaje", p_CodViaje.Trim)
                cmd4.ExecuteNonQuery()
                ' se cambia el estado del vuelo a cancelado
                cmd5 = New SqlCommand("UPDATE V_ProgramaViaje SET EstadoViaje='9' WHERE (CodViaje = @CodViaje)", cn)
                cmd5.Transaction = oTran
                cmd5.Parameters.AddWithValue("@CodViaje", p_CodViaje.Trim)
                cmd5.ExecuteNonQuery()


                oTran.Commit()
                Return True
            Catch myException As Exception
                MsgBox("ManifiestoADO (pr_UpdateManifiestoCancelado) --> " & myException.Message, MsgBoxStyle.Critical)
                oTran.Rollback()
                Return False
            End Try
        End Using
    End Function


    Public Function pr_AgregarCabeceraManifiesto(ByVal p_C_Manifiesto As C_ManifiestoBE) As Boolean
        Try
            Dim oTran As SqlTransaction
            Try
                Dim cn As New SqlConnection(MiConexion.GetCnx)
                Using cn
                    Dim sqlc As String = "INSERT INTO dbo.C_Manifiesto(CodViaje, CodLugarOrigen, CodLugarDestino, CodEmpresa, FchViaje, HraViaje, CodAeropuerto, CodResponsable, ObsCabecera, ObsCabeceraManifiesto, EstadoManifiesto, FlgValija, CodCreadopor, FchCreado, FlgVacio) " & _
                                         "VALUES (@CodViaje, @CodLugarOrigen, @CodLugarDestino, @CodEmpresa, @FchViaje, @HraViaje, @CodAeropuerto, @CodResponsable, @ObsCabecera, @ObsCabeceraManifiesto, @EstadoManifiesto, @FlgValija, @CodCreadopor, @FchCreado, @FlgVacio)"
                    cn.Open()
                    oTran = cn.BeginTransaction(IsolationLevel.Serializable)
                    Dim cmd As New SqlCommand("SELECT COUNT(*) FROM dbo.C_Manifiesto WHERE CodViaje = @CodViaje", cn)
                    Using cmd
                        cmd.CommandType = CommandType.Text
                        cmd.Parameters.Add("@CodViaje", SqlDbType.Char, 12)
                        cmd.Parameters("@CodViaje").Value = p_C_Manifiesto.CodViaje.Trim
                        cmd.Transaction = oTran
                        Dim t As Integer = CInt(cmd.ExecuteScalar())
                        If t > 0 Then
                        Else
                            Dim cmd1 As New SqlCommand(sqlc, cn)
                            Using cmd1
                                cmd1.Parameters.AddWithValue("@CodViaje", p_C_Manifiesto.CodViaje)
                                cmd1.Parameters.AddWithValue("@CodLugarOrigen", p_C_Manifiesto.CodLugarOrigen)
                                cmd1.Parameters.AddWithValue("@CodLugarDestino", p_C_Manifiesto.CodLugarDestino)
                                cmd1.Parameters.AddWithValue("@CodEmpresa", p_C_Manifiesto.CodEmpresa)
                                cmd1.Parameters.AddWithValue("@FchViaje", p_C_Manifiesto.FchViaje)
                                cmd1.Parameters.AddWithValue("@HraViaje", p_C_Manifiesto.HraViaje)
                                cmd1.Parameters.AddWithValue("@CodAeropuerto", p_C_Manifiesto.CodAeropuerto)
                                cmd1.Parameters.AddWithValue("@CodResponsable", p_C_Manifiesto.CodResponsable)
                                cmd1.Parameters.AddWithValue("@ObsCabecera", p_C_Manifiesto.ObsCabecera)
                                cmd1.Parameters.AddWithValue("@ObsCabeceraManifiesto", p_C_Manifiesto.ObsCabeceraManifiesto)
                                cmd1.Parameters.AddWithValue("@EstadoManifiesto", p_C_Manifiesto.EstadoManifiesto)
                                cmd1.Parameters.AddWithValue("@FlgValija", p_C_Manifiesto.FlgValija)
                                cmd1.Parameters.AddWithValue("@CodCreadopor", p_C_Manifiesto.CodCreadopor)
                                cmd1.Parameters.AddWithValue("@FchCreado", p_C_Manifiesto.FchCreado)
                                cmd1.Parameters.AddWithValue("@FlgVacio", True)
                                cmd1.Transaction = oTran
                                cmd1.ExecuteNonQuery()
                            End Using
                        End If
                    End Using
                    oTran.Commit()
                    Return True
                End Using
            Catch myException As Exception
                MsgBox("ManifiestoADO (pr_AgregarCabeceraManifiesto) --> " & myException.Message, MsgBoxStyle.Critical)
                'oTran.Rollback()
                Return False
            End Try
        Catch myException As Exception
            MsgBox("ManifiestoADO (pr_AgregarCabaceraManifiesto) --> " & myException.Message, MsgBoxStyle.Critical)
            Return False
        End Try
    End Function

    Public Function pr_RecuperarListaManifiestoImpresionSinPasajeros(ByVal p_CodViaje As String) As DataTable
        Try
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Using cn
                Dim cmd As New SqlCommand("sp_Listar_Manifiesto_Impresion_SinPasajeros", cn)
                Using cmd
                    cmd.CommandType = Data.CommandType.StoredProcedure
                    cmd.Parameters.Add("@p_CodViaje", SqlDbType.Char, 12)
                    cmd.Parameters("@p_CodViaje").Value = p_CodViaje.Trim
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable("Manifiesto")
                    da.Fill(dt)
                    Return dt
                End Using
            End Using
        Catch myException As Exception
            MsgBox("ManifiestoADO (pr_RecuperarListaManifiestoImpresionSinPasajeros) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function
End Class